package init;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.sql.*;
import javax.sql.*;
import java.io.*;
import javax.naming.*;

@SuppressWarnings("Duplicates")
@WebServlet("/MyServlet")
public class MyServlet extends HttpServlet {

    private static final String URL = "jdbc:h2:mem:~/homework;MODE=MYSQL;DB_CLOSE_DELAY=-1";
    private static final String DRIVER = "org.h2.Driver";
    private static final String NAME = "sa";
    private static final String PASSWORD = "sa";

    public MyServlet() throws NamingException {
    }

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException {
        resp.setContentType("text/html;charset=UTF-8");
        try (Connection conn = DriverManager.getConnection(URL, NAME, PASSWORD)) {
            Statement stmt = conn.createStatement();

            stmt.executeUpdate(
                    "CREATE TABLE IF NOT EXISTS `student` (" +
                            "  `id` int(11) unsigned NOT NULL AUTO_INCREMENT," +
                            "  `course_name` varchar(100) NOT NULL," +
                            "  `grade` double(11) NOT NULL," +
                            "  PRIMARY KEY (`id`)" +
                            ");"
            );

            // 插入数据记录
            stmt.executeUpdate(
                    "insert into `student` (`course_name`,`grade`) values ('语文', 100);" +
                            "insert into `student` (`course_name`,`grade`) values ('数学', 100);" +
                            "insert into `student` (`course_name`,`grade`) values ('英语', 100);"
            );

            ResultSet rs = stmt.executeQuery("select * from student");//选择import java.sql.ResultSet;

            // 遍历每行记录
            while (rs.next()) {
                //如果结果集中有数据，就会循环打印出来
                System.out.println(rs.getInt("id") + "," + rs.getString("course_name") + rs.getDouble("grade"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        resp.sendRedirect("http://localhost:8080/test/student.jsp");
    }
}
